﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace timetable
{
    class Program
    {
        static void Main(string[] args)
        {
            PrintLine();

            PrintRow('Teacher ID','Subject Type ID','Class ID','Subject Code','Total Slot','Max Continuous Slot','Subject Weight');

            GetData();

        }

        static void GetData()
        {
            string connectionString=“server=localhost ;database=timetable ; user ID=root ;pwd=123 ; Integrated Security=true”;

            try 
            {
                string query = “SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                                FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                                WHERE d.class_id='6A'
                                AND d.class_teacher_id=a.teacher_id
                                AND a.subject_type_id=b.subject_type_id
                                AND b.subject_code=c.subject_code
                                AND a.class_id=d.class_id

                                UNION

                                SELECT *
                                FROM(SELECT DISTINCT a.teacher_id, a.subject_type_id, a.class_id, b.subject_code, c.total_slot, c.max_continuous_slot, c.subject_weight
                                FROM teacher_subject_type_allocation AS a, subject_type AS b, subject AS c, class AS d
                                WHERE d.class_id='6A'
                                AND NOT d.class_teacher_id=a.teacher_id
                                AND a.subject_type_id=b.subject_type_id
                                AND b.subject_code=c.subject_code
                                AND a.class_id=d.class_id
                                ORDER BY subject_weight, teacher_id) AS k;”;

                DataTable dt = new DataTable();

                SqlConnection sqlconn = new SqlConnection(connectionString);

                sqlconn.Open();

                SqlCommand sqlcmd = sqlconn.CreateCommand();

                sqlcmd.CommandText = query;

                SqlDataReader sqlreader = sqlcmd.ExecuteReader();

                while (sqlreader.Read())
                {

                    PrintRow(Convert.ToString(sqlreader["a.teacher_id"]), Convert.ToString(sqlreader["a.subject_type_id"]),

                    Convert.ToString(sqlreader["a.class_id"]), Convert.ToString(sqlreader["b.subject_code"]),
                    
                    Convert.ToString(sqlreader["c.total_slot"]), Convert.ToString(sqlreader["c.max_continuous_slot"]),

                    Convert.ToString(sqlreader["c.subject_weight"]));

                    //Console.WriteLine(“\t{0}\t{1}”, sqlreader["StudentID"], sqlreader["Name"]);

                }
                sqlreader.Close();

                sqlconn.Close();

            }

            catch (SqlException e)

            {

                    Console.WriteLine(e.Message);

            }

       }

        static void PrintLine()

        {

            Console.WriteLine(new string(‘-’, 73));

        }

        static void PrintRow(string StudentID, string Name, string Course, string Address)

        {

            Console.WriteLine(

            string.Format(“|{0}| {1}| {2} |{3}|”,

            AlignCentre(StudentID, 17),

            AlignCentre(Name, 17),

            AlignCentre(Course, 17),

            AlignCentre(Address, 17)));

        }
        static string AlignCentre(string text, int width)

        {

            if (string.IsNullOrEmpty(text))

            {

                return new string(‘ ‘, width);

            }

            else

            {

                return text.PadRight(width – (width – text.Length) / 2).PadLeft(width);

            }

        }
    }
}

